/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.exp.table;

/**
 * 不同类型数据库表结构查询的SQL语句。
 *
 * @author SultansOfSwing & MaYue
 */
public interface TableStructExportSQL {
    /**
     * TABLE_DESC_QUERY_FOR_DB2
     */
    String TABLE_DESC_QUERY_FOR_DB2 = 
            "SELECT" + " T1.COLNAME AS \"propertyId\"," + " T1.REMARKS AS \"propertyName\"," + " T1.TYPENAME AS \"dataType\","
            + " CASE WHEN T1.TYPENAME ='INTEGER' THEN 10 WHEN T1.TYPENAME ='SMALLINT' THEN 5 WHEN T1.TYPENAME ='NUMERIC' THEN 31 ELSE T1.LENGTH END AS \"dataLength\","
            + " T1.LENGTH AS \"dataPrecision\"," + " T1.SCALE AS \"dataScale\"," + " T1.NULLS AS \"isNotNull\"," + " NULL AS \"defaultValue\","
            + " MAX(CASE WHEN T2.TYPE='P' THEN '1' ELSE '0' end ) as \"isPrimaryKey\""
            + " FROM SYSCAT.COLUMNS AS T1 LEFT JOIN ( SELECT A.TABSCHEMA, A.TABNAME, B.COLNAME, A.TYPE FROM syscat.tabconst A ,SYSCAT.KEYCOLUSE B WHERE A.CONSTNAME = B.CONSTNAME AND A.TYPE IN ('P','U')) AS T2  ON T1.TABSCHEMA=T2.TABSCHEMA AND T1.TABNAME=T2.TABNAME AND T1.COLNAME=T2.COLNAME WHERE T1.TABNAME = #{varTableId}"
            + " GROUP BY T1.COLNAME, T1.REMARKS, T1.TYPENAME, T1.LENGTH, T1.SCALE, T1.NULLS ORDER BY T1.COLNAME";

    /**
     * TABLE_DESC_QUERY_FOR_MYSQL
     */
    String TABLE_DESC_QUERY_FOR_MYSQL = "select " + " COLUMN_NAME as 'propertyId'," + " COLUMN_COMMENT as 'propertyName'," + " COLUMN_TYPE as 'dataType',"
            + " case when DATA_TYPE='bigint' or DATA_TYPE='tinyint' or DATA_TYPE='smallint' or DATA_TYPE='mediumint' or DATA_TYPE='int' then NUMERIC_PRECISION+1 when DATA_TYPE='decimal' or DATA_TYPE='double' or DATA_TYPE='float' then NUMERIC_PRECISION when DATA_TYPE='varchar' or DATA_TYPE='char' then CHARACTER_MAXIMUM_LENGTH else CHARACTER_OCTET_LENGTH end as 'dataLength',"
            + " NUMERIC_PRECISION as 'dataPrecision'," + " NUMERIC_SCALE as 'dataScale',"
            + " case when COLUMN_KEY='PRI' then '1' else '0' end as 'isPrimaryKey'," + " case when IS_NULLABLE='NO' then '1' else '0' end as 'isNotNull',"
            + " COLUMN_DEFAULT as 'defaultValue'" + " from INFORMATION_SCHEMA.COLUMNS"
            + " where TABLE_NAME = #{varTableId} and TABLE_SCHEMA = #{varSchema} order by ORDINAL_POSITION";

    /**
     * TABLE_DESC_QUERY_FOR_ORACLE
     */
    String TABLE_DESC_QUERY_FOR_ORACLE = 
            "select "
            + "T1.COLUMN_NAME AS \"propertyId\", " + "T2.COMMENTS AS \"propertyName\", " + "T1.DATA_TYPE AS \"dataType\", " + "T1.DATA_LENGTH AS \"dataLength\", " + "T1.DATA_PRECISION AS \"dataPrecision\", "
            + "T1.DATA_SCALE AS \"dataScale\", " + "case when T1.NULLABLE='N' then 1 else 0 end AS \"isNotNull\","
            + "case when T3.CONSTRAINT_TYPE='P' then 1 else 0 end AS \"isPrimaryKey\""
            + " from DBA_TAB_COLUMNS T1 left join DBA_COL_COMMENTS T2 on T1.OWNER = T2.OWNER and T1.TABLE_NAME = T2.TABLE_NAME and T1.COLUMN_NAME = T2.COLUMN_NAME"
            + " left join "
            + " (select  T3.CONSTRAINT_NAME, T3.COLUMN_NAME, T3.TABLE_NAME, T3.OWNER, T4.CONSTRAINT_TYPE, T4.STATUS from  DBA_CONS_COLUMNS T3, DBA_CONSTRAINTS T4 where T3.CONSTRAINT_NAME = T4.CONSTRAINT_NAME and T4.CONSTRAINT_TYPE = 'P' and T4.STATUS = 'ENABLED' and T3.OWNER = T4.OWNER) T3 "
            + " on T1.OWNER = T3.OWNER and T1.TABLE_NAME = T3.TABLE_NAME and T1.COLUMN_NAME = T3.COLUMN_NAME"
            + " where T1.TABLE_NAME = #{varTableId} and T1.OWNER = #{varSchema}" + " order by T1.COLUMN_ID";

    /**
     * TABLE_LIST_QUERY_FOR_ORACLE
     */
    String TABLE_LIST_QUERY_FOR_ORACLE = "select t.table_name from sys.dba_users s left join sys.dba_tables t on s.username=t.owner where s.username = #{varSchema} order by t.table_name ";
}
